﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spCtrlmSetupPermissions]
#-- Purpose:		Creates the ControlM Access Role and assigns required
#--					permissions.
#--	Last Update:	05/15/2012
#--					For a complete history - please review comments in SVN
#-- Called By:		Manual by DBA
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spCtrlmSetupPermissions]
(
	@nt_user_name		sysname		= NULL
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @sSQL nvarchar(MAX)

--- Create the ControlMAccess Database Role (if missing) and Grant required access to that role
SELECT @sSQL = '
USE master;
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''ControlMAccess'' AND type = ''R'')
	CREATE ROLE [ControlMAccess] AUTHORIZATION [dbo];
	
GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO ControlMAccess;
GRANT EXECUTE ON dbo.xp_dirtree TO ControlMAccess;

USE [$(DatabaseName)];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''ControlMAccess'' AND type = ''R'')
	CREATE ROLE [ControlMAccess] AUTHORIZATION [dbo];
	
GRANT EXECUTE ON dbo.spCtrlmExecuteScheduledJob TO ControlMAccess;
GRANT EXECUTE ON dbo.spRestoreDatabase_PointInTime TO ControlMAccess;

USE msdb;
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''ControlMAccess'' AND type = ''R'')
	CREATE ROLE [ControlMAccess] AUTHORIZATION [dbo];
	
EXEC dbo.sp_addrolemember @rolename = ''SQLAgentOperatorRole'', @membername = ''ControlMAccess'';
GRANT SELECT ON dbo.sysjobs TO ControlMAccess;
GRANT SELECT ON dbo.sysjobhistory TO ControlMAccess;
GRANT SELECT ON dbo.sysjobsteps TO ControlMAccess;
GRANT SELECT ON dbo.sysjobstepslogs TO ControlMAccess;
GRANT SELECT ON dbo.backupset TO ControlMAccess;
GRANT SELECT ON dbo.backupmediafamily TO ControlMAccess;
'
EXEC	(@sSQL)

--- If a user is passed in, add the user to the ControlMAccess Role
IF @nt_user_name IS NOT NULL
  BEGIN
	SELECT @sSQL = '
	USE master;
	IF NOT EXISTS (SELECT TOP 1 * FROM sys.server_principals WHERE name = N''' + @nt_user_name + ''')
		CREATE LOGIN [' + @nt_user_name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[$(DatabaseName)];
		
	IF NOT EXISTS (	SELECT TOP 1 pr.name, pm.name
					FROM		sys.database_principals pr
					JOIN		sys.database_role_members m ON pr.principal_id = m.role_principal_id
					JOIN		sys.database_principals pm ON m.member_principal_id = pm.principal_id
					WHERE		pr.name = ''ControlMAccess''
								AND pm.name = ''' + @nt_user_name + '''
	  )
	  BEGIN
		CREATE USER [' + @nt_user_name + '] FOR LOGIN [' + @nt_user_name + '];
		EXEC dbo.sp_addrolemember @rolename = ''ControlMAccess'', @membername = ''' + @nt_user_name + ''';
	  END

	USE [$(DatabaseName)];
	IF NOT EXISTS (	SELECT TOP 1 pr.name, pm.name
					FROM		sys.database_principals pr
					JOIN		sys.database_role_members m ON pr.principal_id = m.role_principal_id
					JOIN		sys.database_principals pm ON m.member_principal_id = pm.principal_id
					WHERE		pr.name = ''ControlMAccess''
								AND pm.name = ''' + @nt_user_name + '''
	  )
	  BEGIN
		CREATE USER [' + @nt_user_name + '] FOR LOGIN [' + @nt_user_name + '];
		EXEC dbo.sp_addrolemember @rolename = ''ControlMAccess'', @membername = ''' + @nt_user_name + ''';
	  END

	USE msdb;
	IF NOT EXISTS (	SELECT TOP 1 pr.name, pm.name
					FROM		sys.database_principals pr
					JOIN		sys.database_role_members m ON pr.principal_id = m.role_principal_id
					JOIN		sys.database_principals pm ON m.member_principal_id = pm.principal_id
					WHERE		pr.name = ''ControlMAccess''
								AND pm.name = ''' + @nt_user_name + '''
	  )
	  BEGIN
		CREATE USER [' + @nt_user_name + '] FOR LOGIN [' + @nt_user_name + '];
		EXEC dbo.sp_addrolemember @rolename = ''ControlMAccess'', @membername = ''' + @nt_user_name + ''';
	  END
	'
	PRINT @sSQL
	EXEC	(@sSQL)
  END

SET NOCOUNT OFF